package lk.icta.core.dao.impl;

import lk.icta.common.dao.impl.BaseDAO;
import lk.icta.common.entity.District;
import lk.icta.common.entity.RailwayTrack;
import lk.icta.core.dao.AlienationRailwayDAO;
import lk.icta.core.entity.AlienationRailway;
import lk.icta.user.entity.User;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import java.util.Date;
import java.util.EnumSet;
import java.util.List;

/**
 * @author Kusal Samarakoon
 * @author Supun Viraj Rathnayaka
 */
public class AlienationRailwayDAOImpl extends BaseDAO implements AlienationRailwayDAO {

    @Transactional(propagation = Propagation.MANDATORY)
    public void addAlienation(AlienationRailway alienation, User user) {
        logger.debug("Add an AlienationRailway for the land {} by {}", alienation.getLand().getLandFileNumber(), user.getUsername());
        alienation.getLifeCycleInfo().setCreatedUser(user);
        alienation.getLifeCycleInfo().setCreatedTimeStamp(new Date());
        alienation.getLifeCycleInfo().setLastUpdatedUser(user);
        alienation.getLifeCycleInfo().setLastUpdatedTimeStamp(new Date());
        em.persist(alienation);
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    public void updateAlienation(AlienationRailway alienation, User user) {
        logger.debug("Update an AlienationRailway for the land {} by {}", alienation.getLand().getLandFileNumber(), user.getUsername());
        alienation.getLifeCycleInfo().setLastUpdatedUser(user);
        alienation.getLifeCycleInfo().setLastUpdatedTimeStamp(new Date());
        em.merge(alienation);
    }

    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public AlienationRailway getAlienationById(Long id, User user) {
        logger.debug("Loading AlienationRailway [Id: {}]", id);
        return em.find(AlienationRailway.class, id);
    }

    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public AlienationRailway getActiveAlienationByLandId(long landId, User user) {
        logger.debug("Find the active alienation of land {}", landId);
        Query q = em.createNamedQuery("getActiveRailwayAlienationByLandId");
        q.setParameter("landId", landId);
        List res = q.getResultList();
        return (res.size() > 0) ? (AlienationRailway) q.getSingleResult() : null;
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<AlienationRailway> getAlienationRailwayByStateSet(User user, EnumSet<AlienationRailway.State> stateSet) {
        Query q = em.createNamedQuery("getAlienationRailwayByStateSetUser");

        q.setParameter("stateSet", stateSet);
        populateDivisionList(q, user);
        q.setParameter("showMigs", 1);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<AlienationRailway> getAlienationRailwayByStateSet(User user, EnumSet<AlienationRailway.State> stateSet, int pageNo, int showMigs, int noOfRows) {
        //Query q = em.createNamedQuery("getAlienationRailwayByStateSetUser");
        Query q = em.createNamedQuery("getAlienationRailwayByStateSetUser").setFirstResult(
                (pageNo - 1) * noOfRows).setMaxResults(noOfRows);

        q.setParameter("stateSet", stateSet);
        populateDivisionList(q, user);
        q.setParameter("showMigs", showMigs);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public int getAllAlienationRailwaysCountByState(User user, EnumSet<AlienationRailway.State> stateSet) {
        logger.debug("getAllAlienationRailwaysCountByState");

        Query q = em.createNamedQuery("getAllAlienationRailwaysCountByState");
        q.setParameter("stateSet", stateSet);
        populateDivisionList(q, user);

        Number result = (Number) q.getSingleResult();
        logger.debug("getAllAlienationRailwaysCountByState {} ", result.intValue());
        return result.intValue();
    }

    @Override
    public List<AlienationRailway> getAlienationRailwayByLeaseId(User user, String leaseId) {
        Query q = em.createNamedQuery("getAlienationRailwayByLeaseId");
        q.setParameter("leaseId", "%" + leaseId + "%");
        populateDivisionList(q, user);
        return q.getResultList();
    }

    @Override
    public List<AlienationRailway> getAlienationRailwayByLeaseIdAndStateSet(User user, String leaseId, EnumSet<AlienationRailway.State> stateSet) {
        Query q = em.createNamedQuery("getAlienationRailwayByLeaseIdAndStateSet");

        q.setParameter("leaseId", "%" + leaseId + "%");
        q.setParameter("stateSet", stateSet);
        populateDivisionList(q, user);
        return q.getResultList();
    }

    @Override
    public List getAlienationRailwayByTrackAndDistrictSet(District district, RailwayTrack railwayTrack) {
        Query q = em.createNamedQuery("getAlienationRailwayByTrackAndDistrict");
        int i = district.getId();
        q.setParameter("districtId",district.getId());
        if(railwayTrack == null){
            q.setParameter("ignoreTrack",1);
            q.setParameter("railwayTrackId",null);
        }else{
            q.setParameter("ignoreTrack",0);
            q.setParameter("railwayTrackId",railwayTrack.getId());
        }
        return q.getResultList();
    }

    @Override
    public List<Object[]> getAlienationStatisticsData(int branchId, int trackId, int districtId, Date startDate, Date endDate, double fromValue, double toValue) {
        String query = "SELECT a.approveState AS leaseState, COUNT(a) AS count FROM AlienationRailway a";

        if (branchId != 0 || trackId != 0 || districtId != 0 || startDate != null || endDate != null || fromValue != 0 || toValue != 0) {
            boolean isAdded = false;
            query += " WHERE ";
            if (branchId != 0) {
                query += "a.land.branch.id =:branchId";
                isAdded = true;
            }

            if (trackId != 0) {
                query += isAdded ? " AND " : "";
                query += "a.land.railwayTrack.id = :trackId";
                isAdded = true;
            }

            if (districtId != 0) {
                query += isAdded ? " AND " : "";
                query += "a.land.district.id =:districtId";
                isAdded = true;
            }

            if(endDate != null && startDate != null){
                query += isAdded ? " AND " : "";
                query += "a.lifeCycleInfo.createdTimeStamp BETWEEN :startDate AND :endDate";
                isAdded = true;
            }else if(startDate == null){
                query += isAdded ? " AND " : "";
                query += "a.lifeCycleInfo.createdTimeStamp <=:endDate";
                isAdded = true;
            }

            if(fromValue != 0 && toValue != 0){
                query += isAdded ? " AND " : "";
                query += "a.valuation.valuationAmount >=:fromValue AND a.valuation.valuationAmount <=:toValue";
                isAdded = true;
            }else if(fromValue != 0){
                query += isAdded ? " AND " : "";
                query += "a.valuation.valuationAmount >=:fromValue";
                isAdded = true;
            }else if(toValue != 0) {
                query += isAdded ? " AND " : "";
                query += "a.valuation.valuationAmount <=:toValue";
                isAdded = true;
            }
        }
        query += " GROUP BY a.approveState";

        Query q = em.createQuery(query);

        if (branchId != 0) q.setParameter("branchId", branchId);
        if (trackId != 0) q.setParameter("trackId", trackId);
        if (districtId != 0) q.setParameter("districtId", districtId);
        if(endDate != null) q.setParameter("endDate", endDate);
        if(startDate != null) q.setParameter("startDate", startDate);

        if (fromValue != 0) q.setParameter("fromValue", fromValue);
        if (toValue != 0) q.setParameter("toValue", toValue);

        logger.debug("Dynamic Query  : {}", query);

        List<Object[]> result = q.getResultList();
        return result;
    }

    @Override
    public List<AlienationRailway> getAlienationDetailsList(int branchId, int trackId, int districtId, Date startDate, Date endDate, int fromValue, int toValue) {
        String query = "SELECT a FROM AlienationRailway a";

        if (branchId != 0 || trackId != 0 || districtId != 0 || startDate != null || endDate != null || fromValue != 0 || toValue != 0) {
            boolean isAdded = false;
            query += " WHERE ";
            if (branchId != 0) {
                query += "a.land.branch.id =:branchId";
                isAdded = true;
            }

            if (trackId != 0) {
                query += isAdded ? " AND " : "";
                query += "a.land.railwayTrack.id = :trackId";
                isAdded = true;
            }

            if (districtId != 0) {
                query += isAdded ? " AND " : "";
                query += "a.land.district.id =:districtId";
                isAdded = true;
            }

            if(endDate != null && startDate != null){
                query += isAdded ? " AND " : "";
                query += "a.lifeCycleInfo.createdTimeStamp BETWEEN :startDate AND :endDate";
                isAdded = true;
            }else if(startDate == null){
                query += isAdded ? " AND " : "";
                query += "a.lifeCycleInfo.createdTimeStamp <=:endDate";
                isAdded = true;
            }

            if(fromValue != 0 && toValue != 0){
                query += isAdded ? " AND " : "";
                query += "a.valuation.valuationAmount >=:fromValue AND a.valuation.valuationAmount <=:toValue";
                isAdded = true;
            }else if(fromValue != 0){
                query += isAdded ? " AND " : "";
                query += "a.valuation.valuationAmount >=:fromValue";
                isAdded = true;
            }else if(toValue != 0) {
                query += isAdded ? " AND " : "";
                query += "a.valuation.valuationAmount <=:toValue";
                isAdded = true;
            }
        }

        Query q = em.createQuery(query);

        if (branchId != 0) q.setParameter("branchId", branchId);
        if (trackId != 0) q.setParameter("trackId", trackId);
        if (districtId != 0) q.setParameter("districtId", districtId);
        if(endDate != null) q.setParameter("endDate", endDate);
        if(startDate != null) q.setParameter("startDate", startDate);

        if (fromValue != 0) q.setParameter("fromValue", (double) fromValue);
        if (toValue != 0) q.setParameter("toValue", (double) toValue);

        logger.debug("Dynamic Query  : {}", query);

        return q.getResultList();
    }

    @Override
    public List<AlienationRailway> getAlienationByStartedDay(int day, int month) {
        Query q = em.createNamedQuery("getAlienationByStartedDay");
        q.setParameter("Day", day);
        q.setParameter("Month", month);
        return q.getResultList();
    }

    @Override
    public List<AlienationRailway> getAlienationByValuationStartDay(int day, int month) {
        Query q = em.createNamedQuery("getAlienationByValuationStartDay");
        q.setParameter("Day", day);
        q.setParameter("Month", month);
        return q.getResultList();
    }

    @Override
    public List<AlienationRailway> getAlienationStatisticsByFilters(int branchId, int trackId, int districtId, long minAmnt, long maxAmnt) {
        String query = "SELECT a FROM AlienationRailway a";

        if (branchId != 0 || trackId != 0 || districtId != 0 || minAmnt != 0 || maxAmnt != 0) {
            boolean isAdded = false;
            query += " WHERE ";
            if (branchId != 0) {
                query += "a.land.branch.id =:branchId";
                isAdded = true;
            }

            if (trackId != 0) {
                query += isAdded ? " AND " : "";
                query += "a.land.railwayTrack.id = :trackId";
                isAdded = true;
            }

            if (districtId != 0) {
                query += isAdded ? " AND " : "";
                query += "a.land.district.id =:districtId";
                isAdded = true;
            }

            if(minAmnt != 0 && maxAmnt != 0){
                query += isAdded ? " AND " : "";
                query += "a.leaseDetail.arrearsAmount >=:minAmnt AND a.leaseDetail.arrearsAmount <=:maxAmnt";
                isAdded = true;
            }else if(minAmnt != 0){
                query += isAdded ? " AND " : "";
                query += "a.leaseDetail.arrearsAmount >=:minAmnt";
                isAdded = true;
            }else if(maxAmnt != 0) {
                query += isAdded ? " AND " : "";
                query += "a.leaseDetail.arrearsAmount <=:maxAmnt";
                isAdded = true;
            }
        }

        Query q = em.createQuery(query);

        if (branchId != 0) q.setParameter("branchId", branchId);
        if (trackId != 0) q.setParameter("trackId", trackId);
        if (districtId != 0) q.setParameter("districtId", districtId);

        if (minAmnt != 0) q.setParameter("minAmnt", (double) minAmnt);
        if (maxAmnt != 0) q.setParameter("maxAmnt", (double) maxAmnt);

        logger.debug("Dynamic Query  : {}", query);

        return q.getResultList();
    }

    @Override
    public List<AlienationRailway> getAlienationsByIdRnage(long start, long end) {
        Query q = em.createNamedQuery("getAlienationsByIdRnage");
        q.setParameter("start", start);
        q.setParameter("end", end);
        return q.getResultList();
    }

    @Override
    public List<AlienationRailway> getAlienationRailwayByFilters(int branchId, int trackId, int districtId) {
        String query = "SELECT a FROM AlienationRailway a";

        if (branchId != 0 || trackId != 0 || districtId != 0) {
            boolean isAdded = false;
            query += " WHERE ";
            if (branchId != 0) {
                query += "a.land.branch.id =:branchId";
                isAdded = true;
            }

            if (trackId != 0) {
                query += isAdded ? " AND " : "";
                query += "a.land.railwayTrack.id = :trackId";
                isAdded = true;
            }

            if (districtId != 0) {
                query += isAdded ? " AND " : "";
                query += "a.land.district.id =:districtId";
                isAdded = true;
            }
        }
            Query q = em.createQuery(query);

            if (branchId != 0) q.setParameter("branchId", branchId);
            if (trackId != 0) q.setParameter("trackId", trackId);
            if (districtId != 0) q.setParameter("districtId", districtId);

            logger.debug("Dynamic Query : {}", query);

            return q.getResultList();
    }

    @Override
    public List<AlienationRailway> getAlienationLikeReferenceNo(String referenceNo) {
        Query q = em.createNamedQuery("getAlienationReferenceNoLike");
        q.setParameter("refNo", "%"+referenceNo+"%");
        return q.getResultList();
    }

    private void populateDivisionList(Query q, User user) {

        if (user != null && user.getBranch() != null) {

            if (user.getDistricts() != null && user.getDistricts().size() > 0) {
                logger.debug("role : {}", user.getRole().getName());
                logger.debug("ignoreDS : {}", 0);
                q.setParameter("ignoreDS", 0);
                q.setParameter("DSList", user.getDistricts());
                q.setParameter("branchId", user.getBranch().getId());
            } else {
                logger.debug("role  : {}", user.getRole().getName());
                logger.debug("ignoreDS  : {}", 2);
                q.setParameter("ignoreDS", 2);
                q.setParameter("DSList", null);
                q.setParameter("branchId", user.getBranch().getId());
            }


        } else {
            logger.debug("role  : {}", user.getRole().getName());
            logger.debug("ignoreDS  : {}", 1);
            q.setParameter("ignoreDS", 1);
            q.setParameter("DSList", null);
            q.setParameter("branchId", null);
        }

    }


}
